﻿Imports System
Imports System.Data
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.IO
Imports System.Text

Module modSendImagesToDB

    Sub Main()
        Dim strConn As String = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=pion.modspace.com)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=prod8)));User Id=space;Password=space_dev991;"
        Dim myOConn As OracleConnection
        Dim storefile As Directory
        Dim files As String()
        Dim File As String
        Dim iCounter As Int32
        Dim SourceLoc As String
        Try

            myOConn = New OracleConnection(strConn)
            myOConn.Open()
            If myOConn.State = ConnectionState.Open Then
                Console.WriteLine("Connected to database!")
                files = storefile.GetFiles("D:\user\", "*.pdf", SearchOption.TopDirectoryOnly)

                For Each File In files
                    iCounter = iCounter + 1
                    SourceLoc = File
                    Console.WriteLine(File)

                    ' provide read access to the file
                    Dim Fs As FileStream = New FileStream(SourceLoc,
                                           FileMode.Open, FileAccess.Read)

                    ' Create a byte array of file stream length
                    Dim ImageData As Byte()
                    ReDim ImageData(Fs.Length)

                    'Read block of bytes from stream into the byte array
                    Fs.Read(ImageData, 0, System.Convert.ToInt32(Fs.Length))

                    'Close the File Stream
                    Fs.Close()
                    ' Step 3
                    ' Create Anonymous PL/SQL block string
                    Dim block As String = " INSERT INTO Photos (photoid, photoblob, phototitle) VALUES (:1, :2, :3)"

                    ' Set command to create Anonymous PL/SQL Block
                    Dim cmd As OracleCommand = New OracleCommand()
                    cmd.CommandText = block
                    cmd.Connection = myOConn
                    ' Since executing an anonymous PL/SQL block, setting the command type
                    ' as Text instead of StoredProcedure
                    cmd.CommandType = CommandType.Text

                    ' Step 4
                    ' Setting Oracle parameters

                    ' Bind the parameter as OracleDbType.Blob
                    ' to command for inserting image
                    Dim param1 As OracleParameter = cmd.Parameters.Add("id", OracleDbType.Int32)
                    param1.Size = 3
                    param1.Direction = ParameterDirection.Input
                    param1.Value = iCounter

                    Dim param2 As OracleParameter = cmd.Parameters.Add("blobtodb", OracleDbType.Blob)
                    param2.Direction = ParameterDirection.Input
                    ' Assign Byte Array to Oracle Parameter
                    param2.Value = ImageData

                    Dim param3 As OracleParameter = cmd.Parameters.Add("filename", OracleDbType.Varchar2)
                    param3.Size = 50
                    param3.Direction = ParameterDirection.Input
                    ' Assign Byte Array to Oracle Parameter
                    param3.Value = File

                    ' Step 5
                    ' Execute the Anonymous PL/SQL Block
                    ' The anonymous PL/SQL block inserts the image to the database and then retrieves
                    ' the images as an output parameter
                    cmd.ExecuteNonQuery()
                    cmd.Dispose()
                    Console.WriteLine("Image file inserted to database from " + SourceLoc)
                Next
            End If
        Catch ex As Exception
            Console.WriteLine(ex.ToString)
        End Try
    End Sub
End Module
